package cn.edu.cuit.elena.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import cn.edu.cuit.elena.db.DataSource;
import cn.edu.cuit.elena.db.DataSourceException;
import cn.edu.cuit.elena.trace.Tracer;

public class BaseDao
    implements AccessDataModel
{

    private DataSource dataSource;
    private Connection connection;

    public BaseDao(DataSource dataSource)
    {
        this.dataSource = dataSource;
    }

    public void update( String sql )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            Statement statement = connection.createStatement();
            statement.executeUpdate( sql );
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }

    public ResultSet query( String sql )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            Statement statement = connection.createStatement();
            return statement.executeQuery( sql );
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }

    public Map<?, ?> insert( String instruction, Map<?, ?> paramMap )
    {
        return null;
    }

    public Map<?, ?> update( String instruction, Map<?, ?> paramMap )
    {
        return null;
    }

    public Map<?, ?> del( String instruction, Map<?, ?> paramMap )
    {
        return null;
    }

    /**
     * 
     * Query multiple (or one, depends on the parameter) row(s), put result into a <code>list</code>
     * , each element of this list is a <code>Map</code>, which's entry is each column name and
     * value.
     * 
     * <pre>
     * 
     *          List[
     *              row 1 Map{[column 1 name : value, column 2 name : value, column 3 name : value ...]},
     *              row 2 Map{[column 1 name : value, column 2 name : value, column 3 name : value ...]},
     *              row 3 Map{[column 1 name : value, column 2 name : value, column 3 name : value ...]},
     *              ... ...
     *              ]
     * 
     * </pre>
     * */
    public List<Map<String, String>> queryForMap( String sql, String... parameters )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            PreparedStatement statement = connection.prepareStatement( sql );

            if( parameters != null )
            {
                for( int index = 1; index <= parameters.length; index++ )
                {
                    Tracer.debugTrace( getClass(), index + " th. para : " + parameters[index - 1], null );
                    statement.setString( index, parameters[index - 1] );
                }
            }

            ResultSet resultSet = statement.executeQuery();
            List<Map<String, String>> resultList = new LinkedList<Map<String, String>>();

            while( resultSet.next() )
            {
                Map<String, String> row = new HashMap<String, String>();

                int count = resultSet.getMetaData().getColumnCount();

                for( int index = 1; index <= count; index++ )
                {
                    row.put( resultSet.getMetaData().getColumnLabel( index ), resultSet.getString( index ) );
                }

                resultList.add( row );
            }

            Tracer.debugTrace( this.getClass(), "Result totoal rows : " + resultList.size(), null );
            return resultList;
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }

    /**
     * Query <strong>one row</strong> by <strong>PK</strong>, each name of column would be put into
     * map as <strong>key</strong>, each value would be put in to map as <strong>value</strong>.
     * */
    public Map<?, ?> queryForMapOFoneRow( String instruction, String... parameters )
    {
        Tracer.debugTrace( this.getClass(), "sql:" + instruction, null );

        for( String string : parameters )
        {
            Tracer.debugTrace( this.getClass(), "para:" + string, null );
        }

        connection = dataSource.getConnection();

        try
        {
            PreparedStatement statement = connection.prepareStatement( instruction );

            for( int index = 1; index <= parameters.length; index++ )
            {
                statement.setString( index, parameters[index - 1] );
            }

            Map<String, String> map = new HashMap<String, String>();

            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            resultSet.next();

            int columnCount = metaData.getColumnCount();

            for( int index = 1; index <= columnCount; index++ )
            {
                String key = metaData.getColumnName( index );
                String value = resultSet.getString( key );
                map.put( key, value );
            }

            return map;
        }
        catch( Exception e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            return null;
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }

    public List<?> queryForList( String instruction, int paramInt1, int paramInt2 )
    {
        //TODO
        return null;
    }

    public List<?> queryForList( String instruction, Object paramObject, int paramInt1, int paramInt2 )
    {
        //TODO
        return null;
    }

    /**
     * Query for multiple rows for designated parameters, each row would be encapsulated into a list
     * in order by the order in sql.
     * 
     * <pre>
     * return List 
     *     row 1{ List{column 1, column 2, column 3...}}
     *     row 2{ List{column 1, column 2, column 3...}}
     *     row 3{ List{column 1, column 2, column 3...}}
     *     ... ...
     * </pre>
     * */
    public List<List<String>> queryForList( String sql, List<String> parameters )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            PreparedStatement statement = connection.prepareStatement( sql );

            for( int index = 1; index <= parameters.size(); index++ )
            {
                Tracer.debugTrace( getClass(), index + " th. para : " + parameters.get( index - 1 ), null );
                statement.setString( index, parameters.get( index - 1 ).toString() );
            }

            ResultSet resultSet = statement.executeQuery();
            List<List<String>> resultList = new LinkedList<List<String>>();

            while( resultSet.next() )
            {
                List<String> row = new LinkedList<String>();
                int count = resultSet.getMetaData().getColumnCount();

                for( int index = 1; index <= count; index++ )
                {
                    row.add( resultSet.getString( index ) );
                }

                resultList.add( row );
            }

            Tracer.debugTrace( this.getClass(), "Result totoal rows : " + resultList.size(), null );
            return resultList;
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }

    public List<?> queryForList( String instruction )
    {
        return queryForList( instruction, new LinkedList<String>() );
    }

    /**
     * Insert <strong>one row</strong> into database, each <strong>parameter</strong> is
     * corresponding to '<strong>?</strong>' in <strong>pre-compile sql</strong>.
     * */
    public void insertOneRow( String sql, List<? extends Object> parameters )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            PreparedStatement statement = connection.prepareStatement( sql );

            for( int index = 1; index <= parameters.size(); index++ )
            {
                Tracer.debugTrace( getClass(), index + " th. para : " + parameters.get( index - 1 ), null );
                statement.setString( index, parameters.get( index - 1 ).toString() );
            }

            statement.executeUpdate();
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }

    /**
     * Delete row for designated parameters.
     * */
    public void del( String sql, List<? extends Object> parameters )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            PreparedStatement statement = connection.prepareStatement( sql );

            for( int index = 1; index <= parameters.size(); index++ )
            {
                statement.setString( index, parameters.get( index - 1 ).toString() );
            }

            statement.executeUpdate();
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }

    }

    /**
     * The same with insertOneRow
     * */
    public void insert( String sql, List<? extends Object> parameters )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            PreparedStatement statement = connection.prepareStatement( sql );

            for( int index = 1; index <= parameters.size(); index++ )
            {
                Tracer.debugTrace( getClass(), index + " th. para : " + parameters.get( index - 1 ), null );
                statement.setString( index, parameters.get( index - 1 ).toString() );
            }

            statement.execute();
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }

    public void execute( String sql )
    {
        Tracer.debugTrace( this.getClass(), sql, null );

        connection = dataSource.getConnection();

        try
        {
            PreparedStatement statement = connection.prepareStatement( sql );
            statement.execute();
        }
        catch( SQLException e )
        {
            Tracer.fatalTrace( this.getClass(), e.getMessage(), e );
            throw new DataSourceException( e.getMessage() );
        }
        finally
        {
            dataSource.returnConnection( connection );
        }
    }
}
